﻿/*
COPYRIGHT (C) 2008 EPISERVER AB

THIS FILE IS PART OF SCRUM DASHBOARD.

SCRUM DASHBOARD IS FREE SOFTWARE: YOU CAN REDISTRIBUTE IT AND/OR MODIFY IT UNDER THE TERMS OF 
THE GNU LESSER GENERAL PUBLIC LICENSE VERSION v2.1 AS PUBLISHED BY THE FREE SOFTWARE FOUNDATION.

SCRUM DASHBOARD IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY; WITHOUT
EVEN THE IMPLIED WARRANTY OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. SEE THE GNU LESSER
GENERAL PUBLIC LICENSE FOR MORE DETAILS.

YOU SHOULD HAVE RECEIVED A COPY OF THE GNU LESSER GENERAL PUBLIC LICENSE ALONG WITH SCRUM DASHBOARD. 
IF NOT, SEE <HTTP://WWW.GNU.ORG/LICENSES/>.
*/
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;

namespace ScrumDashboard.Library.Entities
{
    public class DBHelper
    {
        private static readonly string connectionString;
        private static readonly string entityConnectionString;

        static DBHelper()
        {
            connectionString = ConfigurationManager.ConnectionStrings["ScrumDashBoard"].ConnectionString;
            if (!connectionString.EndsWith(";"))
                connectionString += ";";
            entityConnectionString = String.Format("metadata=res://*/Entities.ScrumDashboardDB.csdl|res://*/Entities.ScrumDashboardDB.ssdl|res://*/Entities.ScrumDashboardDB.msl;provider=System.Data.SqlClient;provider connection string=\"{0}MultipleActiveResultSets=True\"",connectionString);
        }

        public static void AddWorkItemEvent(string projectName, int id, int revision, bool isNew, bool changedState, string workItemType)
        {
            using (ScrumDashboardEntities e = new ScrumDashboardEntities(entityConnectionString))
            {
                 var proj = (from p in e.Project
                                 where p.ProjectName==projectName
                                 select p).FirstOrDefault();

                if (proj==null)
                {
                    proj = new Project() { ProjectName = projectName }; 
                    e.AddToProject(proj);
                }

                WorkItemEvent ev = new WorkItemEvent();
                ev.WorkItemId = id;
                ev.RevisionId = revision;
                ev.IsNew = isNew;
                ev.ChangedState = changedState;
                ev.WorkItemType = workItemType;
                ev.CreatedDate = DateTime.Now;

                proj.WorkItemEvent.Add(ev);

                e.SaveChanges();
            }
        }

        public static List<WorkItemEvent> GetWorkItemEvents(string project, string type, int count, TimeSpan timePeriod)
        {
            using (ScrumDashboardEntities e = new ScrumDashboardEntities(entityConnectionString))
            {
                DateTime sinceDate = DateTime.Now.Subtract(timePeriod);

                var feed = from ev in e.WorkItemEvent
                                  where 
                                  ev.CreatedDate > sinceDate
                                  && ev.Project.ProjectName==project
                                  && ev.WorkItemType==type
                                  orderby ev.CreatedDate descending
                                  select ev;
                return feed.Take(count).ToList();
            }
        }

        public static void SaveTeam(string projectName, string teamName)
        {
            using (ScrumDashboardEntities e = new ScrumDashboardEntities(entityConnectionString))
            {
                var teamExists = from t in e.Team
                                 where t.TeamName == teamName && t.Project.ProjectName == projectName
                                 select t;

                if (teamExists.Count()==0)
                {
                    Project proj = (from p in e.Project
                                             where p.ProjectName == projectName
                                             select p).First();
                    proj.Team.Add(new Team() { TeamName = teamName });
                    e.SaveChanges();
                }
            }
        }

        public static List<Member> GetTeamMembers(string projectName, string teamName)
        {
            using (ScrumDashboardEntities e = new ScrumDashboardEntities(entityConnectionString))
            {
                var teamMembers = from tm in e.TeamMember
                                  where tm.Team.TeamName == teamName && tm.Team.Project.ProjectName == projectName
                                  select tm.Member;
                return teamMembers.ToList();
            }
        }

        public static void DeleteTeamMember(string projectName, string teamName, string fullName)
        {
            using (ScrumDashboardEntities e = new ScrumDashboardEntities(entityConnectionString))
            {
                var teamMember = (from tm in e.TeamMember
                                  where tm.Team.TeamName == teamName 
                                        && tm.Team.Project.ProjectName == projectName
                                        && tm.Member.FullName==fullName
                                  select tm).First();
                e.DeleteObject(teamMember);
                e.SaveChanges();
            }
        }

        public static void SaveTeamMember(string projectName, string teamName, string fullName)
        {
            using (ScrumDashboardEntities e = new ScrumDashboardEntities(entityConnectionString))
            {
                var memberSearch = from m in e.Member
                             where m.FullName == fullName
                             select m;
                Member member;

                if (memberSearch.Count() == 0)
                {
                    member = new Member() { FullName = fullName };
                    e.AddToMember(member);
                }
                else
                {
                    member = memberSearch.First();
                }

                var team = (from t in e.Team where t.TeamName==teamName select t).First();

                TeamMember tm = new TeamMember();
                tm.Member = member;
                tm.Team = team;
                
                e.AddToTeamMember(tm);
                e.SaveChanges();
            }
        }

        public static void SaveProject(string projectName)
        {
            using (ScrumDashboardEntities e = new ScrumDashboardEntities(entityConnectionString))
            {
                var projectExists = from p in e.Project
                                 where p.ProjectName==projectName
                                 select p;
                if (projectExists.Count() == 0)
                {
                    e.AddToProject(new Project() { ProjectName = projectName });
                    e.SaveChanges();
                }
            }
        }
    }
}
